﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;
using SCRUM.Core.Exceptions.Database;
using System.Data.SqlClient;

namespace SCRUM.Core.DataProviders
{
    /// <summary>
    /// Database data database (user)
    /// </summary>
    public partial class DBDataProvider : DataProvider
    {

        /// <summary>
        /// Gets the users from project specified by it's ID
        /// </summary>
        /// <param name="projectID">The project ID.</param>
        public override IList<UserInRole> GetUsersInRole(int userInRoleID, int projectID, int teamID)
        {
            IList<UserInRole> users = new List<UserInRole>();
            try
            {
                DbParameter dbUserInRoleID = CreateParameter("userInRoleID", userInRoleID);
                DbParameter dbProjectID = CreateParameter("projectID", projectID);
                DbParameter dbTeamID = CreateParameter("teamID", teamID);

                connection.Open();
                DbDataReader data = GetGrid("TeamMember/GetUsers", dbUserInRoleID, dbProjectID, dbTeamID);

                while (data.Read())
                {
                    UserInRole tab = new UserInRole(
                        (int)data["ID"],
                        (int)data["USER_ID"],
                        (string)data["USER_NAME"],
                        (string)data["FULLNAME"],
                        (int)data["ROLE_ID"],
                        (string)data["ROLE_NAME"],
                        (int)data["TEAM_ID"],
                        (string)data["TEAM_NAME"]
                    );
                    users.Add(tab);
                }
            }
            finally
            {
                connection.Close();
            }
            return users; 
        }

        /// <summary>
        /// Adds the user in role.
        /// </summary>
        /// <param name="userID">The user ID.</param>
        /// <param name="roleID">The role ID.</param>
        /// <param name="teamID">The team ID.</param>
        public override void AddUserInRole(int userID, int roleID, int teamID)
        {
            try
            {
                DbParameter dbUserID = CreateParameter("userID", userID);
                DbParameter dbRoleID = CreateParameter("roleID", roleID);
                DbParameter dbTeamID = CreateParameter("teamID", teamID);

                connection.Open();

                DbDataReader data = GetVector("TeamMember/GetUserInTeam", dbUserID, dbTeamID);
                if (data.Read())
                {
                    throw new InconsistentDatabaseException("Cannot insert such user");
                }
                data.Close();

                dbUserID = CreateParameter("userID", userID);
                dbRoleID = CreateParameter("roleID", roleID);
                dbTeamID = CreateParameter("teamID", teamID);

                ExecuteNonQuery("TeamMember/AddUserInRole", dbUserID, dbRoleID, dbTeamID);
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Edits the user in role.
        /// </summary>
        /// <param name="userInRoleID">The user in role ID.</param>
        /// <param name="userID">new user ID.</param>
        /// <param name="roleID">new role ID.</param>
        /// <param name="teamID">new team ID.</param>
        public override void EditUserInRole(int userInRoleID, int userID, int roleID, int teamID)
        {
            try
            {
                
                DbParameter dbUserID = CreateParameter("userID", userID);
                DbParameter dbRoleID = CreateParameter("roleID", roleID);
                DbParameter dbTeamID = CreateParameter("teamID", teamID);

                connection.Open();

                DbDataReader data = GetVector("TeamMember/GetUserInTeam", dbUserID, dbTeamID);
                if (data.Read())
                {
                    // menim team a clovek tam uz je
                    if ((int)data["TEAM_ID"] != teamID)
                    {
                        throw new InconsistentDatabaseException("Cannot insert such user");
                    }
                }
                data.Close();

                DbParameter dbUserInRoleID = CreateParameter("userInRoleID", userInRoleID);
                dbUserID = CreateParameter("userID", userID);
                dbRoleID = CreateParameter("roleID", roleID);
                dbTeamID = CreateParameter("teamID", teamID);

                ExecuteNonQuery("TeamMember/EditUserInRole", dbUserInRoleID, dbUserID, dbRoleID, dbTeamID);
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Deletes the user in role.
        /// </summary>
        /// <param name="userInRoleID">The user in role ID.</param>
        public override void DeleteUserInRole(int userInRoleID)
        {
            try
            {
                DbParameter dbUserInRoleID = CreateParameter("userInRoleID", userInRoleID);

                connection.Open();

                int result = ExecuteNonQuery("TeamMember/DeleteUserInRole", dbUserInRoleID);
                if (result == 0)
                {
                    throw new InconsistentDatabaseException("TeamMemberNotFoundForDelete");
                }
            }
            catch(SqlException)
            {
                throw new InconsistentDatabaseException("TeamMemberNotDeleted");
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
